# -*- coding: utf-8 -*-
import scrapy,sys
import MySQLdb


class QQNumberSpider(scrapy.Spider):
    name = "qqNumber"
    start_urls = ["file:///home/zjx/scrapy/wechat/wechat/source/QQ.html"]


    mysql_host = "rm-wz9326c6mt06wsrigo.mysql.rds.aliyuncs.com"
    mysql_user = "root_zjx"
    mysql_passwd = "zjx285831!@#"

    reload(sys)
    sys.setdefaultencoding('utf-8')

    def parse(self, response):
        # print(response.body.encode('utf-8'))
        qqqunName = response.xpath("//h1[@id='panelTitle-5']/text()").extract_first()
        # print(qqqunName)
        QQNumberSelect = response.xpath("//li[contains(@id, 'search-item-friend')]")
        QQInfoAry = {}
        for qqInfo in QQNumberSelect:
            # print qqInfo.extract()
            qqNumber = qqInfo.xpath('./@_uin').extract_first()
            # print qqNumber
            qqname = qqInfo.xpath("./p[contains(@id,'userNick')]/span//text()").extract_first()
            if qqname == None:
                qqname = qqInfo.xpath("./p[contains(@id,'userNick')]//text()").extract_first()
                qqname = qqname.strip()
            else:
                qqname = qqname.strip().lstrip('(').rstrip(')')
            # print qqname.encode('utf-8')
            if qqNumber != None:
                QQInfoAry[qqNumber] = qqname
        print QQInfoAry
        db = MySQLdb.connect(host=self.mysql_host, user=self.mysql_user,passwd=self.mysql_passwd, charset="utf8")
        cursor = db.cursor()
        cursor.execute("use `%s`;" % "smart_home")
        if len(QQInfoAry) > 0:
            for qq,name in QQInfoAry.items():
                print qq
                print name.encode('utf-8')
                qq = qq.strip()
                name = MySQLdb.escape_string(name)
                email = qq+'@qq.com'
                sql = "insert  into `qq_number_info`(`name`,qq,qq_email,qq_group)VALUES(%s,%s,%s,%s)" % (name,qq,email,qqqunName)
                # sql = MySQLdb.escape_string(sql)
                print sql.encode('utf-8')
                try:
                    n = cursor.execute(sql)
                    print n
                    db.commit()
                except Exception,e:
                    # Rollback in case there is any error
                    print Exception,":",e
                    print "mysql save failed %s" % qq.strip()
                    db.rollback()
                db.close()
            # print(qqNumber+ " " + qqname)
        # filename = response.url.split("/")[-2]
        # with open(filename, 'wb') as f:
        #     f.write(response.body)
